Start of change

ENDED_JOB_INFO table function

Edit online

The ENDED_JOB_INFO table function returns information about jobs that have ended. This information is gathered from CPF1164 messages recorded in the history log.

Authorization: See Note below.

Read syntax diagramSkip visual syntax diagram ENDED_JOB_INFO (START_TIME => start-time,END_TIME => end-time )
The schema is SYSTOOLS.
start-time
A timestamp expression that indicates the starting timestamp to use for returning ended job information.

If this parameter is omitted, the default of CURRENT DATE - 1 DAY is used.

end-time
A timestamp expression that indicates the ending timestamp to use for returning ended job information.

If this parameter is omitted, the default of '9999-12-30-00.00.00.000000' is used.

The result of the function is a table containing rows with the format shown in the following table. All the columns are nullable.
Table 1. ENDED_JOB_INFO table function
Column Name Data Type Description
MESSAGE_TIMESTAMP TIMESTAMP The timestamp when the CPF1164 message was sent.
FROM_USER VARCHAR(10) The user name of the job from which the CPF1164 message was sent.
FROM_JOB VARCHAR(28) The qualified job name from which the CPF1164 message was sent.
FROM_JOB_NAME VARCHAR(10) The name of the job from which the CPF1164 message was sent.
FROM_JOB_USER VARCHAR(10) The user profile that started the job from which the CPF1164 message was sent.
FROM_JOB_NUMBER VARCHAR(6) The job number of the job from which the CPF1164 message was sent.
JOB_INTERFACE VARCHAR(22) The interface used by the job reported by the CPF1164 message.
  • CENTRAL SERVER
  • CLI DB CONNECTION
  • DATA QUEUE SERVER
  • DB2 MIRROR
  • DRDA DDM SERVER TCP/IP
  • FILE SERVER
  • FTP
  • NETSERVER FILE SHARE
  • ODBC OR FILE TXFR
  • OTHER
  • PRINT SERVER
  • REMOTE COMMAND
  • REXEC
  • SIGNON SERVER
  • SNDNETXXX
  • SSH CONNECTION
  • TELNET/5250
FROM_PROGRAM VARCHAR(10) The program that sent the CPF1164 message.
CPU_TIME DECIMAL(15,3) The total amount of processing used by the job, in seconds.
NUMBER_OF_STEPS SMALLINT The number of routing steps used by the job.
JOB_END_CODE SMALLINT The job end code.
0
The job completed normally
10
The job completed normally during controlled ending or controlled subsystem ending
20
The job exceeded end severity (ENDSEV job attribute)
30
The job ended abnormally
40
The job ended before becoming active
50
The job ended while the job was active
60
The subsystem ended abnormally while the job was active
70
The system ended abnormally while the job was active
80
The job ended (ENDJOBABN command)
90
The job was forced to end after the time limit ended (ENDJOBABN command)
JOB_END_DETAIL VARCHAR(100) Descriptive text corresponding to JOB_END_CODE.
SECONDARY_ENDING_CODE SMALLINT The job's secondary ending code.
0
No secondary ending code
100
Disconnect time interval exceeded
101
Session device deleted
102
Error calling Disconnect Job (DSCJOB)
300
Device error and DEVRCYACN set to *ENDJOB
301
Job ended due to looping on device errors
SECONDARY_ENDING_CODE_DETAIL VARCHAR(100) Descriptive text corresponding to SECONDARY_ENDING_CODE.
JOB_ENTERED_SYSTEM_TIME TIMESTAMP(0) The time the job entered the system.

Returns the null value if the value is not available.
JOB_ACTIVE_TIME TIMESTAMP(0) The time the job started to run.

Returns the null value if the value is not available.
TOTAL_RESPONSE_TIME INTEGER The total response time for an interactive job.
SUBSYSTEM VARCHAR(10) Subsystem where the job was run.
INTERACTIVE_TRANSACTIONS INTEGER The total number of operator interactions, such as pressing the Enter key, for the job.
SYNC_AUX_IO_COUNT INTEGER The number of synchronous (physical) disk I/O operations performed by the job. This value is the sum of the synchronous database and non-database reads and writes.
JOB_TYPE VARCHAR(11) The type of job.
AUTOSTART
The job is an autostart job.
BATCH
The job is a batch job.
INTERACTIVE
The job is an interactive job.
MONITOR
The job is a subsystem monitor job.
READER
The job is a spooled reader job.
SCPF
The job is the SCPF system job.
SYSTEM
The job is a system job.
WRITER
The job is a spooled writer job.
PEAK_TEMPORARY_STORAGE INTEGER The maximum temporary storage, in megabytes, used by the job.

Note

This function is provided in the SYSTOOLS schema as an example of how to extract information from message substitution variables. Similar to other Db2® for i provided tools within SYSTOOLS, the SQL source can be extracted and used as a model for building similar helper functions, or to create a customized version within a user-specified schema.

Services provided in SYSTOOLS have authorization requirements that are determined by the interfaces used to implement the service. To understand the authority requirements, extract the SQL for the service and examine the implementation.

Example

List the 10 jobs from yesterday and today that were the top consumers of temporary storage.
SELECT FROM_USER, FROM_JOB, PEAK_TEMPORARY_STORAGE, JOB_ACTIVE_TIME
  FROM TABLE (SYSTOOLS.ENDED_JOB_INFO() )
  ORDER BY PEAK_TEMPORARY_STORAGE DESC
  LIMIT 10;
End of change